<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class XcSiteOrder extends Model
{
    //protected $connection = 'mysql_master';
    protected $table = 'xc_site_order';
    protected $guarded = ['id'];

    public function site()
    {
        return $this->belongsTo('App\Models\XcSite', 'site_id', 'id');
    }

    public function device()
    {
        return $this->hasOne('App\Models\XcDevice', 'device_no', 'device_no');
    }

    public function washmode()
    {
        return $this->hasOne('App\Models\XcSiteWashMode', 'id', 'sw_id');
    }

    public function user()
    {
        return $this->hasOne('App\Models\XcUser', 'openid', 'openid');
    }

    public function rechargeSetting()
    {
        return $this->hasOne('App\Models\XcRechargeSetting', 'id', 'recharge_setting_id');
    }

    public function userrechage()
    {
        return $this->hasOne('App\Models\XcUserRecharge', 'site_order_sn', 'order_sn');
    }

    /**
     * 计算场租方每月利润利润
     * @param $year
     * @return array
     */
    public function yearRenterProfit($year)
    {
        $list = XcSiteOrder::whereRaw("YEAR(created_at) = '{$year}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("MONTH(created_at) AS month , renter_id, sum(renter_profit) as renter_profit")->groupBy('month')->groupBy('renter_id')->get();

        $data = [];
        foreach ($list as $value) {
            $data[$value->month][$value->renter_id] = $value->renter_profit;
        }

        return $data;
    }

    /**
     * 计算场租方每月利润利润
     * @param $year
     * @return array
     */
    public function yearRenterProfitInfo($year)
    {
        $list = XcSiteOrder::whereRaw("YEAR(created_at) = '{$year}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("MONTH(created_at) AS month , sum(renter_profit) as renter_profit, sum(money) as pay_price, count(*) as num, site_id ")->groupBy('month')->groupBy('site_id')->get();

        $data = [];
        foreach ($list as $value){
            $data[$value->site_id][] = [
                'month' => $value->month,
                'renter_profit' => $value->renter_profit
            ];
        }

        return $data;
    }

    /**
     * 计算站点每月利润
     * @param $year
     * @param $site_id
     * @return array
     */
    public function yearRenterProfitBySiteId($year, $site_id)
    {
        $list = XcSiteOrder::whereRaw("YEAR(created_at) = '{$year}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("MONTH(created_at) AS month , sum(renter_profit) as renter_profit, sum(money) as pay_price, count(*) as num ")->where('site_id', $site_id)->groupBy('month')->get();

        $data = [];
        foreach ($list as $value) {
            $data[$value->month] = $value;
        }

        return $data;
    }

    /**
     * 获取某月场租方利润
     * @param $time
     * @return array
     */
    public function monthRenterProfitByRenterId($time)
    {
        $list = XcSiteOrder::whereRaw("DATE_FORMAT(created_at, '%Y-%m') = '{$time}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("renter_id, sum(renter_profit) as renter_profit")->groupBy('renter_id')->get()->toArray();

        return array_column($list, 'renter_profit', 'renter_id');
    }

    /**
     * 获取某月站点利润
     * @param $time
     * @return array
     */
    public function monthRenterProfitBySiteId($time)
    {
        $list = XcSiteOrder::whereRaw("DATE_FORMAT(created_at, '%Y-%m') = '{$time}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("site_id, sum(renter_profit) as renter_profit")->groupBy('site_id')->get()->toArray();

        return array_column($list, 'renter_profit', 'site_id');
    }

    /**
     * 获取某时间段站点利润
     * @param $start_time
     * @param $end_time
     * @return array
     * @internal param $time
     */
    public function timeRenterProfitBySiteId($start_time, $end_time)
    {
        $list = XcSiteOrder::whereRaw("created_at >= '{$start_time}' and created_at < '{$end_time}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("site_id, sum(renter_profit) as renter_profit, DATE_FORMAT(created_at, '%Y-%m') AS month")->groupBy('site_id')->groupBy('month')->get();

        $data = [];
        foreach ($list as $value){
            $data[$value->site_id][] = [
                'month' => $value->month,
                'renter_profit' => $value->renter_profit
            ];
        }

        return $data;
    }

    /**
     * 获取某天站点利润
     * @param $date
     * @return array
     * @internal param $time
     */
    public function dateRenterProfitBySiteId($date)
    {
        $list = XcSiteOrder::whereRaw("DATE(created_at) = '{$date}'")->whereIn('type', [0, 6])->where('money', '>', 0)->where('status', 1)->selectRaw("site_id, sum(renter_profit) as renter_profit")->groupBy('site_id')->get()->toArray();

        return array_column($list, 'renter_profit', 'site_id');
    }

}
